package pl.soulmusic.phylactery.db;

import java.util.ArrayList;
import java.util.Date;

import pl.soulmusic.phylactery.map.Song;

import com.google.android.maps.GeoPoint;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.provider.MediaStore;
import android.text.format.Time;
import android.util.Log;

public class DAO {
	private DatabaseOpenHelper dbhelper;
	private Context ctx;

	public DAO(Context context, CursorFactory factory) {
		dbhelper = new DatabaseOpenHelper(context, factory);
		ctx = context;
	}

	public void clearPlaylist() {
		Log.i(DatabaseOpenHelper.TAG, "Clearing playlist.");

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		db.delete(DatabaseOpenHelper.PLAYLIST_TABLE, null, null);
		db.close();
	}

	public void addSongToPlaylist(long song) {
		Log.i(DatabaseOpenHelper.TAG, "Adding song to playlist: " + song);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.SONG_ID_COLUMN, song);

		db.insert(DatabaseOpenHelper.PLAYLIST_TABLE, null, values);
		db.close();
	}

	public void removeSongFromPlaylist(long song) {
		Log.i(DatabaseOpenHelper.TAG, "Removing song from playlist: " + song);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		String whereClause = DatabaseOpenHelper.SONG_ID_COLUMN + "=? ";
		String[] whereArgs = { String.valueOf(song) };

		db.delete(DatabaseOpenHelper.PLAYLIST_TABLE, whereClause, whereArgs);
		db.close();
	}

	public ArrayList<Long> getPlaylist() {
		Log.i(DatabaseOpenHelper.TAG, "Fetching playlist");

		ArrayList<Long> result = new ArrayList<Long>();

		SQLiteDatabase db = dbhelper.getWritableDatabase();
		String[] columns = { DatabaseOpenHelper.SONG_ID_COLUMN };

		Cursor cursor = db.query(DatabaseOpenHelper.PLAYLIST_TABLE, columns,
				null, null, null, null, DatabaseOpenHelper.POSITION_COLUMN);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			result.add(cursor.getLong(0));
			cursor.moveToNext();
		}

		db.close();

		return result;
	}

	public ArrayList<Song> getLoggedTracks() {
		Log.i(DatabaseOpenHelper.TAG, "Fetching logged tracks");

		ArrayList<Song> result = new ArrayList<Song>();

		SQLiteDatabase db = dbhelper.getReadableDatabase();
		String[] columns = { DatabaseOpenHelper.SONG_ID_COLUMN, "latitude",
				"longitude", "loggedTime" };

		Cursor tracksCursor = db.query(DatabaseOpenHelper.TRACKS_TABLE,
				columns, null, null, null, null, null);

		if (tracksCursor != null) {
			tracksCursor.moveToFirst();

			ContentResolver cr = ctx.getContentResolver();

			String[] songColumns = { MediaStore.Audio.Media.TITLE,
					MediaStore.Audio.Albums.ALBUM,
					MediaStore.Audio.Albums.ARTIST };

			while (!tracksCursor.isAfterLast()) {
				GeoPoint point = new GeoPoint(tracksCursor.getInt(1),
						tracksCursor.getInt(2));
				Time time = new Time();
				time.set(tracksCursor.getLong(3));

				// retrieving song by id
				String[] selectionArgs = { String.valueOf(tracksCursor
						.getLong(0)) };
				String selection = MediaStore.Audio.Media._ID + "=?";

				Cursor songCursor = cr.query(
						MediaStore.Audio.Media.EXTERNAL_CONTENT_URI,
						songColumns, selection, selectionArgs, null);

				if (songCursor != null) {
					songCursor.moveToFirst();
					if (!songCursor.isAfterLast()) {
						String description = songCursor.getString(0) + " - "
								+ songCursor.getString(1) + " - "
								+ songCursor.getString(2);
						Song song = new Song(point, tracksCursor.getInt(0),
								songCursor.getString(0), description, time);
						song.setAlbum(songCursor.getString(1));
						song.setArtist(songCursor.getString(2));
						result.add(song);
					}
				}
				tracksCursor.moveToNext();
			}

		}
		db.close();

		return result;
	}

	public int getNextSongNumber() {
		Log.i(DatabaseOpenHelper.TAG, "Get next song number");

		int result = 0;

		SQLiteDatabase db = dbhelper.getReadableDatabase();

		String[] columns = { DatabaseOpenHelper.VALUE_COLUMN };
		String selection = DatabaseOpenHelper.NAME_COLUMN + "='"
				+ DatabaseOpenHelper.NEXT_SONG_PROPERTY + "'";

		Cursor cursor = db.query(DatabaseOpenHelper.PROPERTIES_TABLE, columns,
				selection, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			result = cursor.getInt(0);
			cursor.moveToNext();
		}

		db.close();

		return result;
	}

	public void setNextSongNumber(int number) {
		Log.i(DatabaseOpenHelper.TAG, "Set next song number to " + number);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		String whereClause = DatabaseOpenHelper.NAME_COLUMN + "='"
				+ DatabaseOpenHelper.NEXT_SONG_PROPERTY + "'";

		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.VALUE_COLUMN, number);

		db.update(DatabaseOpenHelper.PROPERTIES_TABLE, values, whereClause,
				null);

		db.close();
	}

	public int getCurrentSongNumber() {
		Log.i(DatabaseOpenHelper.TAG, "Get current song number");

		int result = 0;

		SQLiteDatabase db = dbhelper.getReadableDatabase();

		String[] columns = { DatabaseOpenHelper.VALUE_COLUMN };
		String selection = DatabaseOpenHelper.NAME_COLUMN + "='"
				+ DatabaseOpenHelper.CURRENT_SONG_PROPERTY + "'";

		Cursor cursor = db.query(DatabaseOpenHelper.PROPERTIES_TABLE, columns,
				selection, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			result = cursor.getInt(0);
			cursor.moveToNext();
		}

		db.close();

		return result;
	}

	public void setCurrentSongNumber(int number) {
		Log.i(DatabaseOpenHelper.TAG, "Set current song number to " + number);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		String whereClause = DatabaseOpenHelper.NAME_COLUMN + "='"
				+ DatabaseOpenHelper.CURRENT_SONG_PROPERTY + "'";

		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.VALUE_COLUMN, number);

		db.update(DatabaseOpenHelper.PROPERTIES_TABLE, values, whereClause,
				null);

		db.close();
	}

	public int getCurrentGPSState() {
		Log.i(DatabaseOpenHelper.TAG, "Get current GPS follow state");

		int result = 0;

		SQLiteDatabase db = dbhelper.getReadableDatabase();

		String[] columns = { DatabaseOpenHelper.VALUE_COLUMN };
		String selection = DatabaseOpenHelper.NAME_COLUMN + "='followGPS'";

		Cursor cursor = db.query(DatabaseOpenHelper.PROPERTIES_TABLE, columns,
				selection, null, null, null, null);
		cursor.moveToFirst();
		while (!cursor.isAfterLast()) {
			result = cursor.getInt(0);
			cursor.moveToNext();
		}

		db.close();

		return result;
	}

	public void setCurrentGPSState(int number) {
		Log.i(DatabaseOpenHelper.TAG, "Set current GPS follow state to "
				+ number);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		String whereClause = DatabaseOpenHelper.NAME_COLUMN + "='followGPS'";

		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.VALUE_COLUMN, number);

		db.update(DatabaseOpenHelper.PROPERTIES_TABLE, values, whereClause,
				null);

		db.close();
	}

	public void logTrack(long songID, GeoPoint heardLocation) {
		Log.i(DatabaseOpenHelper.TAG, "Logging gps played song: " + songID);

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(DatabaseOpenHelper.SONG_ID_COLUMN, songID);
		values.put("latitude", heardLocation.getLatitudeE6());
		values.put("longitude", heardLocation.getLongitudeE6());

		Date now = new Date();
		values.put("loggedTime", now.getTime());

		db.insert(DatabaseOpenHelper.TRACKS_TABLE, null, values);
		db.close();
	}

	public long getLastLoggedTime(long songID) {
		Log.i(DatabaseOpenHelper.TAG, "Fetching last logged time for song: "
				+ songID);

		SQLiteDatabase db = dbhelper.getReadableDatabase();

		long result = 0;

		String[] columns = { "MAX(loggedTime)" };
		String[] selectionArgs = { String.valueOf(songID) };
		String selection = "song_id =?";

		Cursor cursor = db.query(DatabaseOpenHelper.TRACKS_TABLE, columns,
				selection, selectionArgs, null, null, null);

		if (cursor != null && cursor.moveToFirst())
			result = cursor.getLong(0);

		db.close();
		return result;
	}

	public void clearLogged() {
		Log.i(DatabaseOpenHelper.TAG, "Clearing logged tracks.");

		SQLiteDatabase db = dbhelper.getWritableDatabase();

		db.delete(DatabaseOpenHelper.TRACKS_TABLE, null, null);
		db.close();
	}
}
